﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class StoreProvider:DataAccess
    {
        public StoreProvider()
        {
            this.CacheDuration = Globals.Settings.Store.CacheDuration;
            this.EnableCaching = Globals.Settings.Store.EnableCaching;
            this.ConnectionString = Globals.Settings.ConnectionString;
        }

        #region IDataReader --> Object 

            #region IDataReader --> Department
            protected virtual DepartmentDetails GetDepartmentFromReader(IDataReader reader)
            {
                return new DepartmentDetails(
                   (int)reader["DepartmentID"],
                   (DateTime)reader["AddedDate"],
                   reader["AddedBy"].ToString(),
                   reader["Title"].ToString(),
                   (int)reader["Importance"],
                   reader["Description"].ToString(),
                   (int)reader["ParentId"],
                   (int)reader["PictureId"]);
            }

            protected virtual List<DepartmentDetails> GetDepartmentCollectionFromReader(IDataReader reader)
            {
                List<DepartmentDetails> departments = new List<DepartmentDetails>();
                while (reader.Read())
                    departments.Add(GetDepartmentFromReader(reader));
                return departments;
            }
            #endregion

            #region IDataReader --> Product
                protected virtual ProductDetails GetProductFromReader(IDataReader reader, bool readDescription)
                {
                    ProductDetails product = new ProductDetails(
                                    (int)reader["ProductID"],
                                    (DateTime)reader["AddedDate"],
                                    reader["AddedBy"].ToString(),
                                    (int)reader["DepartmentID"],
                                    reader["Title"].ToString(),
                                    reader["CodeProduct"].ToString(),
                                    reader["Orgin"].ToString(),
                                    reader["SKU"].ToString(),
                                    (decimal)reader["UnitPrice"],
                                    null,
                                    (int)reader["DiscountPercentage"],
                                    (int)reader["UnitsInStock"],
                                     (int)reader["Votes"],
                                     (int)reader["TotalRating"],
                                     (int)reader["Quantity"],
                                      (bool)reader["IsShow"],
                                      (bool)reader["IsNew"],
                                      (int)reader["PictureId"]);

                    if (readDescription)
                        product.Description = reader["Description"].ToString();

                    return product;
                }

                protected virtual ProductDetails GetProductFromReader(IDataReader reader)
                {
                    return GetProductFromReader(reader, true);
                }

                protected virtual List<ProductDetails> GetProductCollectionFromReader(IDataReader reader, bool readDescription)
                {
                    List<ProductDetails> products = new List<ProductDetails>();
                    while (reader.Read())
                        products.Add(GetProductFromReader(reader, readDescription));
                    return products;
                }

                protected virtual List<ProductDetails> GetProductCollectionFromReader(IDataReader reader)
                {
                    return GetProductCollectionFromReader(reader, true);
                }
            #endregion

            #region IDataReader --> Orders

                /// <summary>
                /// Returns a new OrderDetails instance filled with the DataReader's current record data
                /// </summary>
                protected virtual OrderDetails GetOrderFromReader(IDataReader reader)
                {
                    return new OrderDetails(
                       (int)reader["OrderID"],
                       (DateTime)reader["AddedDate"],
                       reader["AddedBy"].ToString(),
                       (int)reader["StatusID"],
                        (int)reader["ProductID"],
                       reader["ShippingMethod"].ToString(),
                       (decimal)reader["SubTotal"],
                       (decimal)reader["Shipping"],
                       reader["ShippingFirstName"].ToString(),
                       reader["ShippingLastName"].ToString(),
                       reader["ShippingNumber"].ToString(),
                       reader["ShippingStreet"].ToString(),
                       reader["ShippingWard"].ToString(),
                       reader["ShippingDistrict"].ToString(),
                       reader["ShippingCity"].ToString(),
                       reader["CustomerEmail"].ToString(),
                       reader["CustomerPhone"].ToString(),
                       reader["CustomerFax"].ToString(),
                       reader["TransactionID"].ToString());
                }

                /// <summary>
                /// Returns a collection of OrderDetails objects with the data read from the input DataReader
                /// </summary>
                protected virtual List<OrderDetails> GetOrderCollectionFromReader(IDataReader reader)
                {
                    List<OrderDetails> orders = new List<OrderDetails>();
                    while (reader.Read())
                        orders.Add(GetOrderFromReader(reader));
                    return orders;
                }

                #endregion

        #endregion

           #region Store Method

                #region Method Department
                public List<DepartmentDetails> ListDepartments(int pageIndex, int pageSize)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("DepartmentsList", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                        cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                        cn.Open();
                        return GetDepartmentCollectionFromReader(ExecuteReader(cmd));
                    }
                }

                public int GetDepartmentsCount()
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("Departments_GetCount", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cn.Open();
                        object kq = ExecuteScalar(cmd);
                        if (string.IsNullOrEmpty(kq.ToString()))
                            return 0;
                        else return (int)kq;
                    }
                }

                public List<DepartmentDetails> ListDepartmentsParent()
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("DepartmentsParentList", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cn.Open();
                        return GetDepartmentCollectionFromReader(ExecuteReader(cmd));
                    }
                }

                public List<DepartmentDetails> ListDepartments_By_ParentID(int ParentID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("DepartmentsList_By_ParentID", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ParentID", SqlDbType.Int).Value = ParentID;
                        cn.Open();
                        return GetDepartmentCollectionFromReader(ExecuteReader(cmd));
                    }
                }

                public DepartmentDetails GetDepartmentByID(int departmentID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("DepartmentsGet", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = departmentID;
                        cn.Open();
                        IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                        if (reader.Read())
                            return GetDepartmentFromReader(reader);
                        else
                            return null;
                    }
                }

                public bool DeleteDepartment(int departmentID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("DepartmentsDelete", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = departmentID;
                        cn.Open();
                        int ret = ExecuteNonQuery(cmd);
                        return (ret == 1);
                    }
                }

                public bool UpdateDepartment(DepartmentDetails department)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("DepartmentsUpdate", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = department.ID;
                        cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = department.AddedDate;
                        cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = department.AddedBy;
                        cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = department.Title;
                        cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = department.Importance;
                        cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = department.Description;
                        cmd.Parameters.Add("@ParentID", SqlDbType.Int).Value = department.ParentID;
                        cmd.Parameters.Add("@PictureID", SqlDbType.Int).Value = department.PictureID;
                        cn.Open();
                        int ret = ExecuteNonQuery(cmd);
                        return (ret == 1);
                    }
                }

                public int InsertDepartment(DepartmentDetails department)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("DepartmentsInsert", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = department.AddedDate;
                        cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = department.AddedBy;
                        cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = department.Title;
                        cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = department.Importance;
                        cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = department.Description;
                        cmd.Parameters.Add("@ParentID", SqlDbType.Int).Value = department.ParentID;
                        cmd.Parameters.Add("@PictureID", SqlDbType.Int).Value = department.PictureID;
                        cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Direction = ParameterDirection.Output;
                        cn.Open();
                        int ret = ExecuteNonQuery(cmd);
                        return (int)cmd.Parameters["@DepartmentID"].Value;
                    }
                }

                public int DepartmentsGet_Count_By_ParentID(int ParentID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("Departments_GetCount_By_ParentID", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ParentID", SqlDbType.Int).Value = ParentID;
                        cn.Open();
                        object kq = ExecuteScalar(cmd);
                        if (string.IsNullOrEmpty(kq.ToString()))
                            return 0;
                        else return (int)kq;
                    }
                }

                public DepartmentDetails DepartmentsGetParent(int id)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("DepartmentsGetParent", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
                        cn.Open();
                        IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                        if (reader.Read())
                            return GetDepartmentFromReader(reader);
                        else
                            return null;
                    }
                }

                #endregion

                #region Method Product

                protected virtual string EnsureValidProductsSortExpression(string sortExpression)
                {
                    if (string.IsNullOrEmpty(sortExpression))
                        return "Products.Title ASC";

                    string sortExpr = sortExpression.ToLower();
                    if (!sortExpr.Equals("unitprice") && !sortExpr.Equals("unitprice asc") && !sortExpr.Equals("unitprice desc") &&
                       !sortExpr.Equals("discountpercentage") && !sortExpr.Equals("discountpercentage asc") && !sortExpr.Equals("discountpercentage desc") &&
                       !sortExpr.Equals("addeddate") && !sortExpr.Equals("addeddate asc") && !sortExpr.Equals("addeddate desc") &&
                       !sortExpr.Equals("addedby") && !sortExpr.Equals("addedby asc") && !sortExpr.Equals("addedby desc") &&
                       !sortExpr.Equals("unitsinstock") && !sortExpr.Equals("unitsinstock asc") && !sortExpr.Equals("unitsinstock desc") &&
                       !sortExpr.Equals("title") && !sortExpr.Equals("title asc") && !sortExpr.Equals("title desc") &&
                       !sortExpr.Equals("codeproduct") && !sortExpr.Equals("codeproduct asc") && !sortExpr.Equals("codeproduct desc") &&
                       !sortExpr.Equals("orgin") && !sortExpr.Equals("orgin asc") && !sortExpr.Equals("orgin desc") &&
                        !sortExpr.Equals("quantity") && !sortExpr.Equals("quantity asc") && !sortExpr.Equals("quantity desc"))
                    {
                        sortExpr = "title asc";
                    }
                    if (!sortExpr.StartsWith("Products"))
                        sortExpr = "Products." + sortExpr;
                    if (!sortExpr.StartsWith("Products.title"))
                        sortExpr += ", Products.title asc";
                    return sortExpr;
                }

                public int GetProductCount()
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("Products_GetCount", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cn.Open();
                        object kq = ExecuteScalar(cmd);
                        if (string.IsNullOrEmpty(kq.ToString()))
                            return 0;
                        else return (int)kq;
                    }
                }

                public List<ProductDetails> ListProduct(int pageSize, int pageIndex)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("");
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                        cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                        cn.Open();
                        return GetProductCollectionFromReader(ExecuteReader(cmd), false);
                    }

                }

                public int GetProductCountByDepartment(int departmentID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("Products_GetCountByDepartment", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = departmentID;
                        cn.Open();
                        object kq = ExecuteScalar(cmd);
                        if (string.IsNullOrEmpty(kq.ToString()))
                            return 0;
                        else return (int)kq;
                    }
                }

                public List<ProductDetails> ListProductByDepartment(int departmentID, int pageSize, int pageIndex)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("ProductsGetByDepartments");
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = departmentID;
                        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                        cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                        cn.Open();
                        return GetProductCollectionFromReader(ExecuteReader(cmd), false);
                    }
                }

                public ProductDetails GetProductByID(int productID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("ProductsGet", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = productID;
                        cn.Open();
                        IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                        if (reader.Read())
                            return GetProductFromReader(reader, true);
                        else
                            return null;
                    }
                }

                public List<ProductDetails> GetProductIsShow(bool isshow)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("ProductsGetIsShow", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@IsShow", SqlDbType.Int).Value = isshow;
                        cn.Open();
                        return GetProductCollectionFromReader(ExecuteReader(cmd), false);
                    }
                }

                public List<ProductDetails> GetProductIsNew(bool isnew)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("ProductsGetIsNew", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@IsNew", SqlDbType.Int).Value = isnew;
                        cn.Open();
                        return GetProductCollectionFromReader(ExecuteReader(cmd), false);
                    }
                }

                public string ProductGetDescription(int productID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("Products_GetProductDescription", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = productID;
                        cn.Open();
                        return (string)ExecuteScalar(cmd);
                    }
                }

                public bool DeleteProduct(int productID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("ProductsDelete", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = productID;
                        cn.Open();
                        int ret = ExecuteNonQuery(cmd);
                        return (ret == 1);
                    }
                }

                public bool UpdateProduct(ProductDetails product)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("ProductsUpdate", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = product.ID;
                        cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = product.DepartmentID;
                        cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = product.Title;
                        cmd.Parameters.Add("@Description", SqlDbType.NText).Value = product.Description;
                        cmd.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = product.UnitPrice;
                        cmd.Parameters.Add("@DiscountPercentage", SqlDbType.Int).Value = product.DiscountPercentage;
                        cmd.Parameters.Add("@Orgin", SqlDbType.NVarChar).Value = product.Orgin;
                        cmd.Parameters.Add("@CodeProduct", SqlDbType.NVarChar).Value = product.CodeProduct;
                        cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = product.Quantity;
                        cmd.Parameters.Add("@IsShow", SqlDbType.Bit).Value = product.IsShow;
                        cmd.Parameters.Add("@IsNew", SqlDbType.Bit).Value = product.IsNew;
                        cmd.Parameters.Add("@PictureId", SqlDbType.Bit).Value = product.PictureId;
                        cn.Open();
                        int ret = ExecuteNonQuery(cmd);
                        return (ret == 1);
                    }
                }

                public int InsertProduct(ProductDetails product)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("ProductsInsert", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = product.AddedDate;
                        cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = product.AddedBy;
                        cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = product.DepartmentID;
                        cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = product.Title;
                        cmd.Parameters.Add("@Description", SqlDbType.NText).Value = product.Description;
                        cmd.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = product.UnitPrice;
                        cmd.Parameters.Add("@DiscountPercentage", SqlDbType.Int).Value = product.DiscountPercentage;
                        cmd.Parameters.Add("@CodeProduct", SqlDbType.NVarChar).Value = product.CodeProduct;
                        cmd.Parameters.Add("@Orgin", SqlDbType.NVarChar).Value = product.Orgin;
                        cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = product.Quantity;
                        cmd.Parameters.Add("@IsShow", SqlDbType.Bit).Value = product.IsShow;
                        cmd.Parameters.Add("@IsNew", SqlDbType.Bit).Value = product.IsNew;
                        cmd.Parameters.Add("@PictureId", SqlDbType.Bit).Value = product.PictureId;
                        cmd.Parameters.Add("@ProductID", SqlDbType.Int).Direction = ParameterDirection.Output;
                        cn.Open();
                        int ret = ExecuteNonQuery(cmd);
                        return (int)cmd.Parameters["@ProductID"].Value;
                    }
                }

                public bool RateProduct(int productID, int rating)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("Product_InsertVote", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = productID;
                        cmd.Parameters.Add("@Rating", SqlDbType.Int).Value = rating;
                        cn.Open();
                        int ret = ExecuteNonQuery(cmd);
                        return (ret == 1);
                    }
                }

                public bool DecrementProductUnitsInStock(int productID, int quantity)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("Product_DecrementUnitsInStock", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = productID;
                        cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = quantity;
                        cn.Open();
                        int ret = ExecuteNonQuery(cmd);
                        return (ret == 1);
                    }
                }

                public List<ProductDetails> ListProductForAll(int departmentID, int pageSize, int pageIndex, string sortExpression)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("ProductsListForAll", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        sortExpression = EnsureValidProductsSortExpression(sortExpression);
                        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                        cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                        cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = departmentID;
                        cmd.Parameters.Add("@sortExpression", SqlDbType.NVarChar).Value = sortExpression;
                        cn.Open();
                        return GetProductCollectionFromReader(ExecuteReader(cmd), false);
                    }
                }

                public List<ProductDetails> GetProducts(string sortExpression, int pageIndex, int pageSize)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        sortExpression = EnsureValidProductsSortExpression(sortExpression);
                        int lowerBound = pageIndex * pageSize + 1;
                        int upperBound = (pageIndex + 1) * pageSize;
                        string sql = string.Format(@"SELECT * FROM
                    (
                       SELECT Products.*, 
                          ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum
                       FROM Products
                    ) AllProducts
                       WHERE AllProducts.RowNum BETWEEN {1} AND {2}
                       ORDER BY RowNum ASC", sortExpression, lowerBound, upperBound);
                        SqlCommand cmd = new SqlCommand(sql, cn);
                        cn.Open();
                        return GetProductCollectionFromReader(ExecuteReader(cmd), false);
                    }
                }

                public List<ProductDetails> GetProducts(int departmentID, string sortExpression, int pageIndex, int pageSize)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        sortExpression = EnsureValidProductsSortExpression(sortExpression);
                        int lowerBound = pageIndex * pageSize + 1;
                        int upperBound = (pageIndex + 1) * pageSize;
                        string sql = string.Format(@"SELECT * FROM
                    (
                       SELECT Products.*,
                          ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum
                       FROM Products INNER JOIN
                          Departments ON Products.DepartmentID = Departments.DepartmentID
                       WHERE Products.DepartmentID = {1}
                    ) DepartmentProducts
                       WHERE DepartmentProducts.RowNum BETWEEN {2} AND {3}
                       ORDER BY RowNum ASC", sortExpression, departmentID, lowerBound, upperBound);
                        SqlCommand cmd = new SqlCommand(sql, cn);
                        cn.Open();
                        return GetProductCollectionFromReader(ExecuteReader(cmd), false);
                    }
                }

                public List<ProductDetails> ListProductByDepartmentNoPageIndex(int departmentID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        string str = "";
                        str += "  select Products.* from Products";
                        if (departmentID > 0)
                            str += "   where Products.DepartmentID=" + departmentID.ToString();
                        SqlCommand cmd = new SqlCommand(str, cn);
                        cmd.CommandType = CommandType.Text;
                        cn.Open();
                        return GetProductCollectionFromReader(ExecuteReader(cmd), false);
                    }
                }

                public List<ProductDetails> ListProductBySearch(string name, int departmentID, int fromprice, int endprice)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        string str = "select * from Products";
                        bool isFirst = true;
                        if (!string.IsNullOrEmpty(name) || departmentID > 0 || fromprice > 0 || endprice > 0)
                        {
                            str += "  where  ";
                            if (!string.IsNullOrEmpty(name))
                            {
                                str += "  ltrim(rtrim(Title)) like N'%" + name + "%'   ";
                                isFirst = false;
                            }

                            if (departmentID > 0)
                            {
                                if (isFirst == false)
                                    str += "  and  ";
                                str += "  DepartmentID=" + departmentID.ToString();
                                isFirst = false;
                            }

                            if (fromprice > 0)
                            {
                                if (isFirst == false)
                                    str += "  and  ";
                                str += "  UnitPrice>=" + fromprice.ToString();
                                isFirst = false;
                            }
                            if (endprice > 0)
                            {
                                if (isFirst == false)
                                    str += "  and  ";
                                str += "  UnitPrice<=" + endprice.ToString();
                            }
                        }
                        str += "  order by UnitPrice  ";

                        SqlCommand cmd = new SqlCommand(str, cn);
                        cmd.CommandType = CommandType.Text;
                        cn.Open();
                        return GetProductCollectionFromReader(ExecuteReader(cmd), false);
                    }
                }

                //added date 22/08/2011 by vutdl
                public List<ProductDetails> ListProduct_ByDepartmentID_NoPageIndex(int departmentID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("ProductsList_ByDepartmentID_NoPageIndex", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = departmentID;
                        cn.Open();
                        return GetProductCollectionFromReader(ExecuteReader(cmd), false);
                    }
                }

                /// <summary>
                /// Increments the ViewCount of the specified article
                /// </summary>
                public bool IncrementProductViewCount(int productID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("Products_IncrementViewCount", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = productID;
                        cn.Open();
                        int ret = ExecuteNonQuery(cmd);
                        return (ret == 1);
                    }
                }

                //end added date 22/08/2011 by vutdl

                #endregion

                #region Method Orders
                public List<OrderDetails> ListOrders(int pageSize, int pageIndex)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("OrdersList", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                        cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                        cn.Open();
                        return GetOrderCollectionFromReader(ExecuteReader(cmd));
                    }
                }

                public OrderDetails GetOrderByID(int OrderID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("OrdersGet", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@OrderID", SqlDbType.Int).Value = OrderID;
                        cn.Open();
                        IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                        if (reader.Read())
                            return GetOrderFromReader(reader);
                        else
                            return null;
                    }
                }

                public List<OrderDetails> GetOrdersByStatus(int statusID, int pageSize, int pageIndex)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("OrdersGetByStatus", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@StatusID", SqlDbType.Int).Value = statusID;
                        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                        cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                        cn.Open();
                        return GetOrderCollectionFromReader(ExecuteReader(cmd));
                    }
                }

                public List<OrderDetails> GetOrdersByProductID(int productID, int pageSize, int pageIndex)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("OrdersGetByProductID", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = productID;
                        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                        cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                        cn.Open();
                        return GetOrderCollectionFromReader(ExecuteReader(cmd));
                    }
                }

                public bool DeleteOrder(int orderID)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("OrdersDelete", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@OrderID", SqlDbType.Int).Value = orderID;
                        cn.Open();
                        int ret = ExecuteNonQuery(cmd);
                        return (ret == 1);
                    }
                }

                public bool UpdateOrder(OrderDetails order)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        object shippedDate = order.ShippedDate;
                        if (order.ShippedDate == DateTime.MinValue)
                            shippedDate = DBNull.Value;

                        SqlCommand cmd = new SqlCommand("OrdersUpdate", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@OrderID", SqlDbType.Int).Value = order.ID;
                        cmd.Parameters.Add("@StatusID", SqlDbType.Int).Value = order.StatusID;
                        cn.Open();
                        int ret = ExecuteNonQuery(cmd);
                        return (ret == 1);
                    }
                }

                public int InsertOrder(OrderDetails order)
                {
                    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("OrdersInsert", cn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = order.AddedDate;
                        cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = order.AddedBy;
                        cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = order.ProductID;
                        cmd.Parameters.Add("@StatusID", SqlDbType.Int).Value = order.StatusID;
                        cmd.Parameters.Add("@ShippingMethod", SqlDbType.NVarChar).Value = order.ShippingMethod;
                        cmd.Parameters.Add("@SubTotal", SqlDbType.Money).Value = order.SubTotal;
                        cmd.Parameters.Add("@Shipping", SqlDbType.Money).Value = order.Shipping;
                        cmd.Parameters.Add("@ShippingFirstName", SqlDbType.NVarChar).Value = order.ShippingFirstName;
                        cmd.Parameters.Add("@ShippingLastName", SqlDbType.NVarChar).Value = order.ShippingLastName;
                        cmd.Parameters.Add("@ShippingNumber", SqlDbType.NVarChar).Value = order.ShippingNumber;
                        cmd.Parameters.Add("@ShippingStreet", SqlDbType.NVarChar).Value = order.ShippingStreet;
                        cmd.Parameters.Add("@ShippingWard", SqlDbType.NVarChar).Value = order.ShippingWard;
                        cmd.Parameters.Add("@ShippingDistrict", SqlDbType.NVarChar).Value = order.ShippingDistrict;
                        cmd.Parameters.Add("@ShippingCity", SqlDbType.NVarChar).Value = order.ShippingCity;
                        cmd.Parameters.Add("@CustomerEmail", SqlDbType.NVarChar).Value = order.CustomerEmail;
                        cmd.Parameters.Add("@CustomerPhone", SqlDbType.NVarChar).Value = order.CustomerPhone;
                        cmd.Parameters.Add("@CustomerFax", SqlDbType.NVarChar).Value = order.CustomerFax;
                        cmd.Parameters.Add("@TransactionID", SqlDbType.NVarChar).Value = order.TransactionID;
                        cmd.Parameters.Add("@OrderID", SqlDbType.Int).Direction = ParameterDirection.Output;
                        cn.Open();
                        int ret = ExecuteNonQuery(cmd);
                        return (int)cmd.Parameters["@OrderID"].Value;
                    }
                }

                #endregion

        #endregion

    }
}
